' can i please have help with these. all one problem. thank you :) '

Question

Manu · Accepted Answer

Step 1: Question 6: Create a new database and tables Create a table Categories with columns CategoryID (integer, primary key) and CategoryName (text, not null). Create a table Products with columns ProductID (integer, primary key), ProductName (text, not null), and CategoryID (integer, foreign key referencing Categories table). Insert sample data into the tables Insert categories: Keyboards and Mice into the Categories table. Insert products: Mechanical Keyboard and Wireless Keyboard under the Keyboards category, and Gaming Mouse under the Mice category. Write DELETE statements to delete the Keyboards category Write a DELETE statement to delete all products in the Keyboards category. Write a DELETE statement to delete the Keyboards category from the Categories table. Answer: The first statement will delete all products in the "Keyboards" category, and the second statement will delete the category itself. Here is the SQL script: -- Create categories table CREATE TABLE categories ( category_id INTEGER PRIMARY KEY, category_name TEXT NOT NULL ); -- Create products table CREATE TABLE products ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category_id INTEGER, FOREIGN KEY (category_id) REFERENCES categories (category_id) ); -- Insert sample data into categories INSERT INTO categories (category_id, category_name) VALUES (1, 'Keyboards'); INSERT INTO categories (category_id, category_name) VALUES (2, 'Mice'); -- Insert sample data into products INSERT INTO products (product_id, product_name, category_id) VALUES (1, 'Mechanical Keyboard', 1); INSERT INTO products (product_id, product_name, category_id) VALUES (2, 'Wireless Keyboard', 1); INSERT INTO products (product_id, product_name, category_id) VALUES (3, 'Gaming Mouse', 2); -- Display data before DELETE operations SELECT * FROM categories; SELECT * FROM products; -- Delete products in the Keyboards category DELETE FROM products WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Keyboards'); -- Delete the Keyboards category DELETE FROM categories WHERE category_name = 'Keyboards'; -- Display data after DELETE operations SELECT * FROM categories; SELECT * FROM products; Explanation: This script ensures that you first create the necessary tables and insert sample data. Then, it deletes the related rows in the Products table before deleting the Keyboards category from the Categories table. Step 2: Question 7 Write an INSERT statement that adds this row to the Customers table: email_address : student@shepherd.edu password : (empty string) first_name : Jerry last_name : Smith Use a column list for this statement. Answer: -- Create the Customers table CREATE TABLE Customers ( customer_id INTEGER PRIMARY KEY AUTOINCREMENT, email_address TEXT NOT NULL, password TEXT, first_name TEXT NOT NULL, last_name TEXT NOT NULL ); -- Insert the specified row into the Customers table INSERT INTO Customers (email_address, password, first_name, last_name) VALUES ('student@shepherd.edu', '', 'Jerry', 'Smith'); -- Display all data from the Customers table SELECT * FROM Customers; The output of SQL : Question 8 Write an UPDATE statement that modifies the Customers table. Change the password column to "secret" for the customer with an email address of student@shepherd.edu . Answer: UPDATE Customers SET password = 'secret' WHERE email_address = 'student@shepherd.edu'; SELECT * FROM Customers; Explanation: This SQL UPDATE statement changes the password for the customer whose email_address is student@shepherd.edu to "secret". Question 9 Write an UPDATE statement that modifies the Customers table. Change the password column to "reset" for every customer in the table. If you get an error due to safe-update mode, you can add a LIMIT clause to update the first 50 rows of the table. (This should update all rows in the table). Answer -- Update password for all customers UPDATE Customers SET password = 'reset'; -- If you get an error due to safe-update mode, use the following statement: UPDATE Customers SET password = 'reset' LIMIT 50; Explanation: This SQL UPDATE statement sets the password for every customer in the Customers table to "reset". If you encounter an error due to safe-update mode, the second statement updates the password for the first 50 rows in the table.

AI tools

Study help

Free tools

Earn & more

Computer science

Computer science

Manu

Answer

Extracted Transcript from Images

AI

Study help

Earn & more

Company